USE [dbmigration]
GO
/****** Object:  StoredProcedure [dbo].[sp_record295]    Script Date: 09/11/2013 15:00:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_record295]
(
	@varStartPeriod DATETIME,	--MONTH
	@varEndPeriod DATETIME
)
/*
	DECLARE @varStartPeriod DATETIME
	DECLARE @varEndPeriod DATETIME
	
	SET @varStartPeriod = '06-23-2013'
	SET @varEndPeriod  = '06-29-2013'
*/
AS
	DECLARE @varRecordType Varchar(15)
	DECLARE @varCompanyCode Varchar(15)
	DECLARE @varTalxClientId Varchar(15)

BEGIN

	SELECT @varRecordType = column_value
	FROM tx_reference_values
	WHERE record_type = 'tx_295EMPCTPAYCHK'
	AND column_name = 'rectype'
	
	SELECT @varCompanyCode = column_value
	FROM tx_reference_values
	WHERE record_type = 'tx_295EMPCTPAYCHK'
	AND column_name = 'cocode'
	
	SELECT @varTalxClientId = column_value
	FROM tx_reference_values
	WHERE record_type = 'tx_295EMPCTPAYCHK'
	AND column_name = 'tci_id'
	
	CREATE TABLE #TMP_295RECORD
	(
		rectype VARCHAR(15),
		cocode  varchar(16),
		ssn  varchar(11),
		filler  varchar(64),
		tci_id  varchar(5),
		filler_1  varchar(100),
		ticsworksitelocnumber  varchar(25),
		ticspaychecknumber  varchar(15),
		ticspaycheckdate  DATETIME,
		ticscusthours  varchar(7),
		ticscustwages  varchar(12),
		EMPNO VARCHAR(15),
		SequenceNumber INT,
		CheckNumber VARCHAR(50),
		EmpKey INT,
		Rate VARCHAR(50)
	)
	
	/*
	SELECT EmpKey, CheckDate, SequenceNumber, CheckNumber, EmployeeNumber, *
	FROM PEOPLESOFT.DBO.Emp_Check2Header
	
	SELECT EmpKey, CheckDate, SequenceNumber, CurrentHours, Rate, ChargeToDept, CurrentEarnings, *
	FROM PEOPLESOFT.DBO.Emp_Check2Earnings
	*/
	
	INSERT INTO #TMP_295RECORD
	(
			rectype,			cocode,				tci_id,				ticsworksitelocnumber,		ticspaycheckdate,			
			ticscusthours,		ticscustwages,		SequenceNumber,		CheckNumber,				EmpKey,
			EMPNO,				Rate
	)
	SELECT	@varRecordType,		@varCompanyCode,	@varTalxClientId,	E.ChargeToDept,				H.CheckDate, 
			E.CurrentHours,		E.CurrentEarnings,	H.SequenceNumber,	H.CheckNumber,				H.EmpKey,	
			H.EmployeeNumber,	E.Rate
	FROM [disys-dev01].PEOPLESOFT.DBO.Emp_Check2Header H, [disys-dev01].PEOPLESOFT.DBO.Emp_Check2Earnings E
	WHERE H.EmpKey = E.EmpKey
	AND H.CheckDate = E.CheckDate
	AND H.SequenceNumber = E.SequenceNumber
	AND CONVERT(DATE, H.CheckDate) BETWEEN @varStartPeriod AND @varEndPeriod
	
	UPDATE T
	SET t.ssn = m.SSN
	FROM #TMP_295RECORD T, [disys-dev01].PEOPLESOFT.DBO.EMP_MAIN M
	WHERE T.EMPNO = M.EmpNo
	
	-----------------------------------
	--HANDLE WORKSITE (WHICH MAY BE CHARGETODEPARTMENT FIELD)
	-----------------------------------
	
/*
	SELECT DISTINCT EMPNO--EMPKEY	--UNIQUE
	FROM PEOPLESOFT.DBO.EMP_MAIN
	
	SELECT *
	FROM PEOPLESOFT.DBO.Emp_Check2Header
	ORDER BY CHECKDATE DESC
*/

	-----------------------------------
	--INSERT DATA IN RECORD 295 TABLE
	-----------------------------------
	
	SELECT *
	FROM #TMP_295RECORD
	
	--DROP TABLE #TMP_295RECORD
	
END
